package com.interfaces;

import java.util.List;

import javax.transaction.Transactional;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

import com.entity.Meter;

/**
 * @Title: MeterDao.java
 * @Package com.interfaces
 * @author 孙梦楠
 * @date 2017年7月19日 下午4:17:42
 * @version V1.0.0
 */

public interface MeterDao extends JpaRepository<Meter, Integer> {

	@Transactional
	@Query(value = "SELECT m FROM Meter m WHERE (m.metercode=?1 OR ?1 IS NULL ) "
			+ "AND (m.meterassetcode=?2 OR ?2 IS NULL ) AND (m.metertype=?3 OR ?3 IS NULL ) "
			+ "AND (m.meteraddress=?4 OR ?4 IS NULL ) AND (m.meterprotocol=?5 OR ?5 IS NULL ) "
			+ "AND (m.metermade=?6 OR ?6 IS NULL ) AND (m.meterorg=?7 OR ?7 IS NULL) "
			+ "AND (m.meterstatus=?8 OR ?8 IS NULL ) AND (m.meterpassword=?9 OR ?9 IS NULL ) AND(m.type=?10)")
	public List<Meter> findByConditions(String metercode, String meterassetcode, String metertype, String meteraddress,
			String meterprotocol, String metermade, Integer meterorg, String meterstatus, String meterpassword,
			String type);

	Meter findByMetercodeAndMeterstatusNot(String metercode, String meterstatus);

	Meter findByMeterassetcodeAndMeterstatusNot(String meterassetcode, String meterstatus);

	Meter findByMeteraddressAndMeterstatusNot(String meteraddress, String meterstatus);

	List<Meter> findByMeterorgAndTypeAndMeterstatus(Integer meterorg, String type, String meterstatus);

	List<Meter> findByMeterorgAndTypeAndMeterstatusNot(Integer meterorg, String type, String meterstatus);

	List<Meter> findByTypeAndMeterstatusNot(String type, String meterstatus);

	// 更新表状态
	@Transactional
	@Modifying
	@Query("update Meter m set m.meterstatus = ?1 where m.meterid = ?2")
	int updateMeter(String meterstatus, Integer meterid);

	// 列出所有没有设置过总分类型的表列表
	@Transactional
	@Query(value = "SELECT m FROM Meter m WHERE  m.meterorg=?1 AND m.type=?2  "
			+ "AND  m.meterid NOT IN (SELECT cm.meterid FROM ChildMeter cm ) ")
	public List<Meter> listmeter2(Integer meterorg, String type);
	//列出符合org的所有未关联总表
	@Transactional
	@Query(value = "SELECT m FROM Meter m WHERE  m.meterorg=?1 AND m.type=?2  "
			+ "AND  m.meterid IN (SELECT cm.meterid FROM ChildMeter cm WHERE cm.type = 1 AND cm.fathermeterid IS NULL) ")
	public List<Meter> listfathermeter(Integer meterorg, String type);
	
	//列出符合org的所有未关联分表
	@Transactional
	@Query(value = "SELECT m FROM Meter m WHERE  m.meterorg=?1 AND m.type=?2  "
			+ "AND  m.meterid IN (SELECT cm.meterid FROM ChildMeter cm WHERE cm.type = 2 AND cm.fathermeterid IS NULL) ")
	public List<Meter> listchildmeter(Integer meterorg, String type);
	
	//列出符合org的所有已关联总表
	@Transactional
	@Query(value = "SELECT m FROM Meter m WHERE  m.meterorg=?1 AND m.type=?2  "
			+ "AND  m.meterid IN (SELECT cm.meterid FROM ChildMeter cm WHERE cm.type = 1 AND cm.fathermeterid IS NOT NULL) ")
	public List<Meter> listconnectedfathermeter(Integer meterorg, String type);

}
